from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/toutiao'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False

db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user_basic'

    class STATUS:
        ENABLE = 1
        DISABLE = 0

    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
    password = db.Column(db.String, doc='密码')
    name = db.Column('user_name', db.String, doc='昵称')
    profile_photo = db.Column(db.String, doc='头像')
    last_login = db.Column(db.DateTime, doc='最后登录时间')
    is_media = db.Column(db.Boolean, default=False, doc='是否是自媒体')
    is_verified = db.Column(db.Boolean, default=False, doc='是否实名认证')
    introduction = db.Column(db.String, doc='简介')
    certificate = db.Column(db.String, doc='认证')
    article_count = db.Column(db.Integer, default=0, doc='发帖数')
    following_count = db.Column(db.Integer, default=0, doc='关注的人数')
    fans_count = db.Column(db.Integer, default=0, doc='被关注的人数（粉丝数）')
    like_count = db.Column(db.Integer, default=0, doc='累计点赞人数')
    read_count = db.Column(db.Integer, default=0, doc='累计阅读人数')

    account = db.Column(db.String, doc='账号')
    email = db.Column(db.String, doc='邮箱')
    status = db.Column(db.Integer, default=1, doc='状态，是否可用')

class Relation(db.Model):
    """
    用户关系表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        # 代表关注
        FOLLOW = 1
        # 拉黑
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    target_user_id = db.Column(db.Integer, doc='目标用户ID')
    relation = db.Column(db.Integer, doc='关系')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')

if __name__ == '__main__':
    # 1.查询手机号码为18516952650的用户
    # sql: select * from user_basic where mobile='18516952650';
    # 1.1 filter:
    # from sqlalchemy.orm import load_only
    # user = User.query.options(load_only(User.id, User.mobile, User.name)).filter(User.mobile == '18516952650').first()
    # 1.2 filter_by:
    # user = User.query.filter_by(mobile='18516952650').first()
    # print(user.id, user.name, user.mobile)

    # 2.查询手机号以13开始的,并且发布文章的数量大于10篇的用户
    # sql:select * from user_basic where mobile like '13%' and article_count > 10;
    # 2.1 filter:
    # from sqlalchemy import and_
    # users = User.query.filter(and_(User.mobile.startswith('13'), User.article_count>10)).all()
    # for user in users:
    #     print(user.id, user.name, user.mobile)
    # 2.2 filter_by:做不到

    # 3.查询手机号以13结尾的,或者发布文章的数量大于10篇的用户
    # sql: select * from user_basic where mobile like '%13' or article_count >10;
    # 3.1filter:
    # from sqlalchemy import or_
    # users = User.query.filter(or_(User.mobile.endswith('13'), User.article_count>10)).all()
    # for user in users:
    #     print(user.id, user.name, user.mobile)
    # 3.2 filter_by:做不到

    # 4.查询手机号不是以13结尾的用户
    # sql: select * from user_basic where mobile not like '%13';
    # 4.1 filter:
    # from sqlalchemy import not_
    # users= User.query.filter(not_(User.mobile.endswith('13'))).all()
    # for user in users:
    #     print(user.id, user.name, user.mobile)
    # print(len(users))
    # 4.2 filter_by:做不到

    # 5.查询手机号不是以13结尾的用户,获取前面的第3条开始的5条数据
    # sql: select * from user_basic where mobile not like '%13' limit 3, 5;
    # 5.1filter:
    # from sqlalchemy import not_
    # users = User.query.filter(not_(User.mobile.endswith('13'))).offset(3).limit(5)
    # for user in users:
    #     print(user.id, user.name, user.mobile)

    # 6.查询手机号不是以13结尾的用户,以发布文章数量排序(正/降),获取前面的第3条开始的5条数据
    # sql: select user_id, user_name from user_basic where mobile not like '%13' order by article_count limit 3, 5;
    # 6.1 filter:
    # from sqlalchemy import not_
    # users = User.query.filter(not_(User.mobile.endswith('13'))).order_by(User.article_count.desc()).offset(3).limit(5)
    # for user in users:
    #     print(user.id, user.name, user.mobile, user.article_count)

    # 7.查询关系表,以user_id分组,查出每个用户当前关注数量
    # sql: select user_id, count(target_user_id) from user_relation group by user_id;
    # 7.1 filter: 没法写
    # 需要用到原生sqlalchemy
    from sqlalchemy import func
    data = db.session.query(Relation.user_id, func.count(Relation.target_user_id)).group_by(Relation.user_id).all()
    for i in data:
        print(i[0], i[1])
        print(data)